package com.hjl.dao.impl;

import com.hjl.dao.UserDao;
import com.hjl.domain.User_inf;
import com.hjl.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.JdbcUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * 管理账户数据访问层
 *
 * @author Lenovo
 * @version 1.0
 * @date 2021/10/8 13:58
 */
public class UserDaoImpl implements UserDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    /**
     * 登录
     *
     * @param loginname 登录名
     * @param password  密码
     * @return com.hjl.domain.User_inf
     * @author Lenovo
     * @date 2021/10/8 14:16
     */
    @Override
    public User_inf findByNameAndPassword(String loginname, String password) {
        //先在外面定义（防止空指针异常）
        User_inf u = null;
        try {
            //binary 密码区分大小写
            String sql = "select * from user_inf where loginname=? and binary PASSWORD=?";
            u = template.queryForObject(sql, new BeanPropertyRowMapper<>(User_inf.class), loginname, password);
        } catch (Exception ignored) {
        }
        return u;
    }

    /**
     * 查找（弃用）
     *
     * @param condition 状态
     * @return java.util.List<com.hjl.domain.User_inf>
     * @author Lenovo
     * @date 2021/10/8 14:16
     */
    @Override
    public List<User_inf> findByCondition(Map<String, String[]> condition) {
        //定义初始sql
        String sql = "select * from user_inf where 1=1 ";
        StringBuilder sb = new StringBuilder(sql);
        //遍历map
        Set<String> keySet = condition.keySet();
        //定义参数集合
        List<Object> params = new ArrayList<>();
        for (String key : keySet) {
            String value = condition.get(key)[0];
            if (value != null && !"".equals(value)) {
                sb.append(" and ").append(key).append(" like ? ");
                params.add("%" + value + "%");
            }
        }
        return template.query(sb.toString(), new BeanPropertyRowMapper<User_inf>(User_inf.class), params.toArray());
    }

    /**
     * 删除
     *
     * @param id ID
     * @author Lenovo
     * @date 2021/10/8 14:16
     */
    @Override
    public void delUser(int id) {
        String sql = "delete from user_inf where ID = ?";
        template.update(sql, id);
    }

    /**
     * 查找总页数
     *
     * @param condition 状态
     * @return int
     * @author Lenovo
     * @date 2021/10/8 14:17
     */
    @Override
    public int findTotalCount(Map<String, String[]> condition) {
        String sql = "select count(id) from user_inf where 1=1 ";
        StringBuilder sb = new StringBuilder(sql);
        //遍历map
        Set<String> keySet = condition.keySet();
        //定义参数的集合
        List<Object> params = new ArrayList<>();
        for (String key : keySet) {
            //排除分页的参数
            if ("currentPage".equals(key) || "rows".equals(key)) {
                continue;
            }
            //获取value
            String value = condition.get(key)[0];
            if (value != null && !"".equals(value)) {
                sb.append(" and ").append(key).append(" like ? ");
                params.add("%" + value + "%");
            }
        }
        System.out.println(sb.toString());
        System.out.println(params);

        return template.queryForObject(sb.toString(), Integer.class, params.toArray());
    }

    /**
     * 查找当前页的数据集合
     *
     * @param start     起始页
     * @param rows      每页记录数
     * @param condition 状态
     * @return java.util.List<com.hjl.domain.User_inf>
     * @author Lenovo
     * @date 2021/10/8 14:02
     */
    @Override
    public List<User_inf> findByPage(int start, int rows, Map<String, String[]> condition) {
        //定义模板初始化sql
        String sql = "select * from user_inf where 1 = 1 ";
        StringBuilder sb = new StringBuilder(sql);
        //遍历map
        Set<String> keySet = condition.keySet();
        //定义参数的集合
        List<Object> params = new ArrayList<>();
        for (String key : keySet) {
            //排除分页的参数
            if ("currentPage".equals(key) || "rows".equals(key)) {
                continue;
            }
            //获取value
            String value = condition.get(key)[0];
            if (value != null && !"".equals(value)) {
                sb.append(" and ").append(key).append(" like ? ");
                params.add("%" + value + "%");
            }
        }
        sb.append(" limit ? , ?");
        params.add(start);
        params.add(rows);
        System.out.println(sb.toString());
        System.out.println(params);
        return template.query(sb.toString(), new BeanPropertyRowMapper<>(User_inf.class), params.toArray());
    }

    /**
     * 更新
     *
     * @param user 用户对象
     * @author Lenovo
     * @date 2021/10/8 14:17
     */
    @Override
    public void updateUser(User_inf user) {
        String sql = "update user_inf set loginname=?,PASSWORD=?,STATUS=?,username=? where id=?";
        template.update(sql, user.getLoginname(), user.getPassword(), user.getStatus(), user.getUsername(), user.getId());
    }

    /**
     * 通过id查找（用于编辑）
     *
     * @param id ID
     * @return com.hjl.domain.User_inf
     * @author Lenovo
     * @date 2021/10/8 14:18
     */
    @Override
    public User_inf findUserById(int id) {
        String sql = "select * from user_inf where id = ?";
        return template.queryForObject(sql, new BeanPropertyRowMapper<>(User_inf.class), id);
    }

    /**
     * 添加
     *
     * @param user 用户对象
     * @author Lenovo
     * @date 2021/10/8 14:18
     */
    @Override
    public void addUser(User_inf user) {
        String sql = "insert into user_inf values(null,?,?,?,now(),?)";
        template.update(sql, user.getLoginname(), user.getPassword(), user.getStatus(), user.getUsername());
    }
}
